package com.meixun.xuanke.handle.dao;

import com.meixun.xuanke.handle.model.Course;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

public interface CourseDao {

    /**
     * 查询某个学生的某个课程的记录
     * @param courseId
     * @return
     */
    @Select("SELECT * FROM x_student_course sc WHERE sc.courseid =#{arg0}  AND sc.studentid=#{arg1} AND sc.termid =#{arg2};")
    HashMap<String,Object> getStuCourse(@Param("arg0")Integer courseId, @Param("arg1")String stuUserId, @Param("arg2")Integer termId);

    /**
     * 修改某个学生的课程的记录
     * @param state
     * @param insertTime
     * @param inputer
     * @return
     */
    @Update("UPDATE x_student_course SET state=#{arg0},insertTime= #{arg1},inputer=#{arg2} WHERE id=#{arg3}")
    int updateStuCourse(@Param("arg0")int state,
                        @Param("arg1")Date insertTime,
                        @Param("arg2")String inputer,
                        @Param("arg3")String id);


    /**
     * 新增某个学生的某个课程的操作日志
     * @param schoolId
     * @param termId
     * @param courseId
     * @param code
     * @param text
     * @param insertTime
     * @param inputer
     * @return
     */
    @Insert("INSERT INTO x_record (schoolid,termid,courseid,code,text,insertTime,inputer)" +
            " VALUES(#{arg0},#{arg1},#{arg2},#{arg3},#{arg4},#{arg5},#{arg6});")
    int addStuCourseRecord(@Param("arg0")String schoolId,
                               @Param("arg1")Integer termId,
                           @Param("arg2")Integer courseId,
                           @Param("arg3")int code,
                           @Param("arg4")String text,
                           @Param("arg5")Date insertTime,
                           @Param("arg6")String inputer);

    /**
     * 查询某个课程的名称
     * @param courseId
     * @return
     */
    @Select("SELECT c.id,CONCAT(s.`name`,c.`name`) coursename FROM x_course c LEFT JOIN x_subject s ON c.subId=s.id WHERE c.id =#{arg0};")
    Course getCourseName(@Param("arg0")Integer courseId);

    /**
     * 查询某个课程的version
     * @param courseId
     * @return
     */
    @Select("SELECT c.version from x_course c WHERE c.id=#{arg0};")
    Integer queryCourseVersion(@Param("arg0")Integer courseId);

    /**
     * 修改某个课程的version
     * @param courseId
     * @return
     */
    @Update("UPDATE x_course SET version =version+1 WHERE id=#{arg0} AND version = #{arg1} " +
            "AND (select COUNT(sc.id) as num from x_student_course sc where sc.courseid=#{arg0} and sc.state<>0)<=size")
    Integer updateCourseVersion(@Param("arg0")Integer courseId,@Param("arg1")Integer version);

    /**
     * 查询某个学生的某个课程的记录
     * @param courseId
     * @return
     */
    @Select("SELECT * FROM x_student_course sc WHERE sc.studentid=#{arg1} AND sc.courseid =#{arg0} AND sc.termid =#{arg2};")
    HashMap<String,Object> queryStuCourse(@Param("arg0")Integer courseId,@Param("arg1")String stuUserId,@Param("arg2")Integer termId);


    /**
     * 新增某个学生的某个课程的记录
     * @param schoolId
     * @param stuUserId
     * @param termId
     * @param courseId
     * @param state
     * @param insertTime
     * @param inputer
     * @return
     */
    @Insert("INSERT INTO x_student_course (schoolid,studentid,termid,courseid,state,insertTime,inputer)" +
            " VALUES(#{arg0},#{arg1},#{arg2},#{arg3},#{arg4},#{arg5},#{arg6});")
    int addStuCourse(@Param("arg0")String schoolId,
                     @Param("arg1")String stuUserId,
                     @Param("arg2")Integer termId,
                     @Param("arg3")Integer courseId,
                     @Param("arg4")int state,
                     @Param("arg5")Date insertTime,
                     @Param("arg6")String inputer);


    /**
     * 查询学校选课配置
     * @param schoolId
     * @return
     */
    @Select("select * from x_config where schoolid=#{arg0}")
    HashMap<String,Object> getConfig(@Param("arg0") String schoolId);
    /**
     * 查询学校选课配置
     * @param termTypeid
     * @return
     */
    @Select("select * from x_term_type where schoolid=#{arg0}")
    HashMap<String,Object> getXTermType(@Param("arg0") Integer termTypeid);


    /**
     * 查询某个学生某个课程是否往期已选
     * @param courseId
     * @param stuUserId
     * @return
     */
    @Select("SELECT sc.courseid,sc.studentid,sc.inputer FROM x_student_course sc LEFT JOIN x_course cou ON cou.id=sc.courseid " +
            "WHERE sc.state!=0 AND sc.studentid =#{arg1} AND cou.subId=(SELECT cou1.subId FROM x_course cou1 WHERE cou1.id=#{arg0}) ;")
    List< HashMap<String,Object>> queryMyCourseByCouId(@Param("arg0")Integer courseId, @Param("arg1")String stuUserId);


    @Select("SELECT cou.id id,cou.subId,CONCAT(sub.`name`,cou.`name`) coursename,sub.price,sub.grade,cou.type,cou.stime,cou.etime,cou.timeText,cou.timeJson,cou.size maxsize,cou.localName,cou.text courseText,sub.text subText," +
            "(SELECT GROUP_CONCAT(t.TeacherName,',') FROM teacher t WHERE FIND_IN_SET(t.UserID,cou.teacher)) teacherName FROM x_course cou LEFT JOIN x_subject sub ON sub.id=cou.subId WHERE cou.id=#{arg0}")
    Course getCourseById(@Param("arg0") Integer id);

    /**
     * 查询某个课程的数量
     * @param courseId
     * @return
     */
    @Select("select COUNT(sc.id) as num from x_student_course sc where sc.courseid=#{arg0} and sc.state<>0;")
    Long getCourseSize(@Param("arg0")Integer courseId);


    /**
     * 查询学生某学期的选课
     * @return
     */
    @Select("SELECT c.id,CONCAT(s.`name`,c.`name`) coursename,c.timeText,c.timeJson,c.subId FROM x_student_course sc LEFT JOIN x_course c ON c.id=sc.courseid LEFT JOIN x_subject s ON s.id=c.subId WHERE" +
            " sc.studentid =#{arg0} AND sc.schoolid=#{arg1} AND sc.termid =#{arg2} and state!=0;")
    List<Course> getMyCourseList(@Param("arg0")String studentid,@Param("arg1")String schoolid,@Param("arg2")Integer termid);

}
